package icst.cyber.hrm.mapper;

import icst.cyber.hrm.dto.JobDTO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class JobMapperDB extends MapperDB {

	public JobMapperDB() throws Exception {
		super();
	}
	
	public JobMapperDB(Connection con) {
		super(con);
	}
	
	public List<JobDTO> getAllJob(){
		log.debug("JobMapperDB.getAllJob.start");
		
		JobDTO jobDTO = null;
		List<JobDTO> result = new ArrayList<JobDTO>();
		String query = "";
		ResultSet rs = null;
		PreparedStatement pStat = null;
		
		try {
			query = "select * from job";
			pStat = getConnection().prepareStatement(query);
			rs = pStat.executeQuery();
			
			while(rs.next()){
				jobDTO = new JobDTO();
				jobDTO.setId(rs.getInt("id"));
				jobDTO.setName(rs.getString("name"));
				result.add(jobDTO);
			}
		} catch (Exception ex) {
			log.error("JobMapperDB.getAllJob", ex);
		} finally {
			try {
				rs.close();
				pStat.close();
			} catch (SQLException sqlEx) {
				log.error("JobMapperDB.getAllJob", sqlEx);
			}
		}
		
		log.debug("JobMapperDB.getAllJob.end");
		return result;
	}
	
	public JobDTO getOneJob(int id){
		log.debug("JobMapperDB.getAllJob.start");
		
		JobDTO jobDTO = null;
		String query = "";
		ResultSet rs = null;
		PreparedStatement pStat = null;
		
		try {
			query = "select * from job where id=?";
			pStat = getConnection().prepareStatement(query);
			pStat.setInt(1, id);
			rs = pStat.executeQuery();
			
			while(rs.next()){
				jobDTO = new JobDTO();
				jobDTO.setId(rs.getInt("id"));
				jobDTO.setName(rs.getString("name"));
			}
		} catch (Exception ex) {
			log.error("JobMapperDB.getAllJob", ex);
		} finally {
			try {
				rs.close();
				pStat.close();
			} catch (SQLException sqlEx) {
				log.error("JobMapperDB.getAllJob", sqlEx);
			}
		}
		
		log.debug("JobMapperDB.getAllJob.end");
		return jobDTO;
	}
	
	public boolean insertJob(String name){
		log.debug("JobMapperDB.insertJob.start");
		
		boolean flag = false;
		String query = "";
		PreparedStatement pStat = null;
		
		try {
			query = "insert into `job` values(NULL, ?)";
			pStat = getConnection().prepareStatement(query);
			pStat.setString(1, name);
			int row = pStat.executeUpdate();
			
			if(row > 0){
				flag = true;
			}
		} catch (Exception ex) {
			log.error("JobMapperDB.insertJob", ex);
		} finally {
			try {
				pStat.close();
			} catch (SQLException sqlEx) {
				log.error("JobMapperDB.insertJob", sqlEx);
			}
		}
		
		log.debug("JobMapperDB.insertJob.end");
		return flag;
	}
	
	public boolean updateJob(int id, String name){
		log.debug("JobMapperDB.updateJob.start");
		
		boolean flag = false;
		String query = "";
		PreparedStatement pStat = null;
		
		try {
			query = "update `job` set `name`=? where `id`=?";
			pStat = getConnection().prepareStatement(query);
			pStat.setString(1, name);
			pStat.setInt(2, id);
			int row = pStat.executeUpdate();
			
			if(row > 0){
				flag = true;
			}
		} catch (Exception ex) {
			log.error("JobMapperDB.updateJob", ex);
		} finally {
			try {
				pStat.close();
			} catch (SQLException sqlEx) {
				log.error("JobMapperDB.updateJob", sqlEx);
			}
		}
		
		log.debug("JobMapperDB.updateJob.end");
		return flag;
	}
	
	public boolean deleteJob(int id){
		log.debug("JobMapperDB.deleteJob.start");
		
		boolean flag = false;
		String query = "";
		PreparedStatement pStat = null;
		
		try {
			query = "delete from `job` where `id`=?";
			pStat = getConnection().prepareStatement(query);
			pStat.setInt(1, id);
			int row = pStat.executeUpdate();
			
			if(row > 0){
				flag = true;
			}
		} catch (Exception ex) {
			log.error("JobMapperDB.deleteJob", ex);
		} finally {
			try {
				pStat.close();
			} catch (SQLException sqlEx) {
				log.error("JobMapperDB.deleteJob", sqlEx);
			}
		}
		
		log.debug("JobMapperDB.deleteJob.end");
		return flag;
	}
}
